package com.yitianyike.lockscreen.ces.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.yitianyike.lockscreen.ces.dao.CardDataDao;
import com.yitianyike.lockscreen.ces.model.Anchor;

@Component("cardDataDao")
public class CardDataDaoImpl extends BaseDAO implements CardDataDao {

	@Override
	public List<Anchor> findAnchorData(Map<String, String> paramMap) {

		StringBuilder sql = new StringBuilder();
		String cids = paramMap.get("cids").toString();
		sql.append(
				"SELECT co.column_id,an.season,an.anchor_id,an.card_style,co.column_name,an.quote_name,la.little_type,la.type,an.frequence,an.exp_date_time,	co.skip_action as skip_action FROM cms_channel ch,cms_package pa,cms_column co,cms_anchor an ,cms_layout la WHERE ch.channel_code=pa.channel_code AND pa.package_id=co.package_id AND co.anchor_id=an.anchor_id AND ch.channel_code=:channel_code  AND co.column_id in( "
						+ cids
						+ " ) AND pa.`status`=1 AND co.`status`=1 AND an.`status`=1 AND la.l_id=an.layout_id    AND pa.use_range=1");

		List<Anchor> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new RowMapper<Anchor>() {
					@Override
					public Anchor mapRow(ResultSet rs, int rowNum) throws SQLException {
						Anchor anchor = new Anchor();
						String column_name = rs.getString("column_name");
						String quote_name = rs.getString("quote_name");
						String season = rs.getString("season");
						int anchor_id = rs.getInt("anchor_id");
						int card_style = rs.getInt("card_style");
						int id = rs.getInt("column_id");
						int little_type = rs.getInt("little_type");
						long exp_date_time = rs.getLong("exp_date_time");
						int type = rs.getInt("type");
						int frequence = rs.getInt("frequence");
						anchor.setAnchor_id(anchor_id);
						anchor.setId(id);
						anchor.setQuote_name(quote_name);
						anchor.setCard_style(card_style);
						anchor.setLittle_type(little_type);
						anchor.setColumn_name(column_name);
						anchor.setExp_date_time(exp_date_time);
						anchor.setFrequence(frequence);
						anchor.setType(type);
						anchor.setSkip_action(rs.getInt("skip_action"));
						anchor.setSeason(season);
						return anchor;
					}
				});

		return resultList;
	}

	@Override
	public List<Map<String, Object>> organizeBasketBalls(Map<String, String> paramMap) {

		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT * FROM `cms_basketball` b WHERE (b.guestname=:quote_name OR b.hostname=:quote_name) AND b.season=:season");

		if (paramMap.containsKey("flush_style")) {
			sql.append(" AND date=CURDATE()");
		}

		List<Map<String, Object>> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						Map<String, Object> resultMap = new HashMap<String, Object>();
						resultMap.put("id", rs.getString("nid"));
						resultMap.put("hostname", rs.getString("hostname"));
						resultMap.put("hostlogo", rs.getString("hostlogo"));
						resultMap.put("hostscore", rs.getInt("hostscore"));
						resultMap.put("type", rs.getString("type"));
						resultMap.put("guestname", rs.getString("guestname"));
						resultMap.put("guestlogo", rs.getString("guestlogo"));
						resultMap.put("guestscore", rs.getInt("guestscore"));
						int status = rs.getInt("status");
						String statusString = (status == 1 || status == 0) ? (status == 0 ? "未开始" : "进行中") : "已结束";
						resultMap.put("status", statusString);
						resultMap.put("date", rs.getString("date"));
						resultMap.put("time", rs.getString("time"));
						resultMap.put("rnd", "");
						resultMap.put("matchurl", rs.getString("matchurl"));
						resultMap.put("package_title", "NBA");
						resultMap.put("package_type", "NBA");
						return resultMap;
					}
				});

		return resultList;
	}

	@Override
	public List<Map<String, Object>> organizeFootBalls(Map<String, String> paramMap) {

		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT * FROM `cms_football` b WHERE (b.guestname=:quote_name OR b.hostname=:quote_name) AND b.season=:season");

		if (paramMap.containsKey("flush_style")) {
			sql.append(" AND date=CURDATE()");
		}

		List<Map<String, Object>> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						Map<String, Object> resultMap = new HashMap<String, Object>();
						resultMap.put("id", rs.getString("nid"));
						resultMap.put("hostname", rs.getString("hostname"));
						resultMap.put("hostlogo", rs.getString("hostflag"));
						resultMap.put("hostscore", rs.getInt("hostscore"));
						resultMap.put("type", "");
						resultMap.put("guestname", rs.getString("guestname"));
						resultMap.put("guestlogo", rs.getString("guestflag"));
						resultMap.put("guestscore", rs.getInt("guestscore"));
						int status = rs.getInt("status");
						String statusString = (status == 1 || status == 0) ? (status == 0 ? "未开始" : "进行中") : "已结束";
						resultMap.put("status", statusString);
						resultMap.put("date", rs.getString("date"));
						resultMap.put("time", rs.getString("time"));
						resultMap.put("rnd", rs.getString("rnd_cn"));
						resultMap.put("matchurl", rs.getString("matchurl"));
						resultMap.put("package_title", rs.getString("package_title"));
						resultMap.put("package_type", rs.getString("package_type"));
						return resultMap;
					}
				});

		return resultList;
	}

	@Override
	public List<Map<String, Object>> flushSportCardData(String channel_code) {

		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT ch.channel_code,co.column_id as id,an.frequence,an.season FROM cms_channel ch,cms_package pa,cms_column co,cms_anchor an  WHERE ch.channel_code=pa.channel_code AND pa.package_id=co.package_id AND co.anchor_id=an.anchor_id  AND pa.`status`=1 AND co.`status`=1 AND an.`status`=1  AND an.season!='no_season' AND ch.channel_code=:channel_code   AND pa.use_range=1");
		Map<String, String> paramMap = new HashMap<String, String>();
		paramMap.put("channel_code", channel_code);
		List<Map<String, Object>> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						Map<String, Object> resultMap = new HashMap<String, Object>();
						String channel_code = rs.getString("channel_code");
						String frequence = rs.getString("frequence");
						int id = rs.getInt("id");
						resultMap.put("channel_code", channel_code);
						resultMap.put("frequence", frequence);
						resultMap.put("last_time", new Date().getTime());
						resultMap.put("aid", id);
						return resultMap;
					}
				});
		return resultList;
	}

	@Override
	public List<Map<String, Object>> getBasketBalls(Map<String, String> paramMap) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT * FROM `cms_basketball` b WHERE (b.guestname=:quote_name OR b.hostname=:quote_name) AND b.season=:season AND date <=CURDATE() ORDER BY date desc LIMIT 2");

		List<Map<String, Object>> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						Map<String, Object> resultMap = new HashMap<String, Object>();
						resultMap.put("id", rs.getString("nid"));
						resultMap.put("hostname", rs.getString("hostname"));
						resultMap.put("hostlogo", rs.getString("hostlogo"));
						resultMap.put("hostscore", rs.getInt("hostscore"));
						resultMap.put("type", rs.getString("type"));
						resultMap.put("guestname", rs.getString("guestname"));
						resultMap.put("guestlogo", rs.getString("guestlogo"));
						resultMap.put("guestscore", rs.getInt("guestscore"));
						int status = rs.getInt("status");
						String statusString = (status == 1 || status == 0) ? (status == 0 ? "未开始" : "进行中") : "已结束";
						resultMap.put("status", statusString);
						resultMap.put("date", rs.getString("date"));
						resultMap.put("time", rs.getString("time"));
						resultMap.put("rnd", "");
						resultMap.put("matchurl", rs.getString("matchurl"));
						resultMap.put("package_title", "NBA");
						resultMap.put("package_type", "NBA");
						return resultMap;
					}
				});

		return resultList;
	}

	public List<Map<String, Object>> getFootBalls(Map<String, String> paramMap) {

		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT * FROM `cms_football` b WHERE (b.guestname=:quote_name OR b.hostname=:quote_name) AND b.season=:season AND date <=CURDATE() ORDER BY date desc LIMIT 2");

		List<Map<String, Object>> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						Map<String, Object> resultMap = new HashMap<String, Object>();
						resultMap.put("id", rs.getString("nid"));
						resultMap.put("hostname", rs.getString("hostname"));
						resultMap.put("hostlogo", rs.getString("hostflag"));
						resultMap.put("hostscore", rs.getInt("hostscore"));
						resultMap.put("type", "");
						resultMap.put("guestname", rs.getString("guestname"));
						resultMap.put("guestlogo", rs.getString("guestflag"));
						resultMap.put("guestscore", rs.getInt("guestscore"));
						int status = rs.getInt("status");
						String statusString = (status == 1 || status == 0) ? (status == 0 ? "未开始" : "进行中") : "已结束";
						resultMap.put("status", statusString);
						resultMap.put("date", rs.getString("date"));
						resultMap.put("time", rs.getString("time"));
						resultMap.put("rnd", rs.getString("rnd_cn"));
						resultMap.put("matchurl", rs.getString("matchurl"));
						resultMap.put("package_title", rs.getString("package_title"));
						resultMap.put("package_type", rs.getString("package_type"));
						return resultMap;
					}
				});

		return resultList;
	}
}
